﻿using ExcelDataReader;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using project.B12.IRepository;
using project.B12.IRepository.Base;
using project.B12.IService;
using project.B12.Model.Enums;
using project.B12.Model.Models;
using software.Common.MVVM;
using SqlSugar;
using System.Data;
using System.Data.SQLite;
using System.Text;
using System.Windows;

namespace project.B12.Service
{
    public partial class WorkSequence : ObservableObject, IWorkSequence
    {
        private readonly IPublicResource publicResource;
        private readonly IUnitOfWork unitOfWork;
        private readonly ISIRRepository sIRRepository;
        private readonly IBacteriaRepository bacteriaRepository;
        private readonly IMedicineRepository medicineRepository;
        private readonly IMonitorMesRepository monitorMesRepository;
        private readonly IServiceDevice serviceDevice;

        public WorkSequence(
            IPublicResource publicResource,
            IUnitOfWork unitOfWork,
            ISIRRepository _sIRRepository,
            IBacteriaRepository _bacteriaRepository,
            IMedicineRepository _medicineRepository,
            IMonitorMesRepository _monitorMesRepository,
            IServiceDevice _serviceDevice)
        {
            this.publicResource = publicResource;
            this.unitOfWork = unitOfWork;
            sIRRepository = _sIRRepository;
            bacteriaRepository = _bacteriaRepository;
            medicineRepository = _medicineRepository;
            monitorMesRepository= _monitorMesRepository;
            serviceDevice = _serviceDevice;

            //Export();
            //AddRunning();
            //addddddd();
            //d12();
            //ddddd();
        }

        private void AddRunning()
        {
            for(int i=1;i<5;i++)
            {
                for(int j=1;j<17;j++)
                {
                    monitorMesRepository.Add(new Table_MonitorMes
                    {
                        Shelf=i,
                        Index=j,
                        State=Model.Enums.HoleState.NULL
                    });
                }
            }
        }

        private void addddddd()
        {
            bacteriaRepository.Add(new Table_bacteria
            {
                CN_Name = "表皮葡萄球菌",
                Kingdom = "细菌界",
                Phylum = "厚壁菌门",
                Class = "芽孢杆菌纲",
                Order = "芽胞杆菌目",
                Family = "葡萄球菌科",
                Genus = "葡萄球菌属",
                Species = "表皮葡萄球菌",
                Read = 1
            });

        }
        private void d12()
        {
            sIRRepository.Add(new Table_SIR
            {
                CN_Name = "葡萄球菌属",
                BacteriaLevel=BacteriaLevel.属,
                BacteriaLevelID=1,
                Target = "万古霉素",
               
                S = "≤2",
                I = "4-8",
                R = "≥16",
            });

            sIRRepository.Add(new Table_SIR
            {
                CN_Name = "葡萄球菌属",
                BacteriaLevel = BacteriaLevel.属,
                BacteriaLevelID = 1,
                Target = "青霉素",
                MedicineID =1 ,
                S = "≤0.12",
                I = "-",
                R = "≥0.25",
            });
            sIRRepository.Add(new Table_SIR
            {
                CN_Name = "葡萄球菌属",
                BacteriaLevel = BacteriaLevel.属,
                BacteriaLevelID = 1,
                Target = "左氧氟沙星",
                MedicineID =2 ,
                S = "≤1",
                I = "2",
                R = "≥4",
            });
            sIRRepository.Add(new Table_SIR
            {
                CN_Name = "葡萄球菌属",
                BacteriaLevel = BacteriaLevel.属,
                BacteriaLevelID = 1,
                Target = "利奈唑胺",
                MedicineID = 11,
                S = "≤4",
                I = "-",
                R = "≥8",
            });
            sIRRepository.Add(new Table_SIR
            {
                CN_Name = "葡萄球菌属",
                BacteriaLevel = BacteriaLevel.属,
                BacteriaLevelID = 1,
                Target = "克林霉素",
                MedicineID = 5,
                S = "≤0.5",
                I = "1-2",
                R = "≥4",
            });
            sIRRepository.Add(new Table_SIR
            {
                CN_Name = "葡萄球菌属",
                BacteriaLevel = BacteriaLevel.属,
                BacteriaLevelID = 1,
                Target = "红霉素",
                MedicineID = 8,
                S = "≤0.5",
                I = "1-4",
                R = "≥8",
            });
            sIRRepository.Add(new Table_SIR
            {
                CN_Name = "金黄葡萄球菌",
                Target = "万古霉素",
                S = "≤4",
                I = "8-16",
                R = "≥32",
            });
            sIRRepository.Add(new Table_SIR
            {
                CN_Name = "肠球菌科",
                Target = "万古霉素",
                S = "≤4",
                I = "8-16",
                R = "≥32",
            });
            sIRRepository.Add(new Table_SIR
            {
                CN_Name = "肠球菌科",
                Target = "青霉素",
                MedicineID =1 ,
                S = "≤8",
                I = "-",
                R = "≥16",
            });
            sIRRepository.Add(new Table_SIR
            {
                CN_Name = "肠球菌科",
                Target = "左氧氟沙星",
                S = "≤2",
                I = "4",
                R = "≥8",
            });
           
            sIRRepository.Add(new Table_SIR
            {
                CN_Name = "肠球菌科",
                Target = "氨卡西林",
                S = "≤8",
                I = "-",
                R = "≥16",
            });
            sIRRepository.Add(new Table_SIR
            {
                CN_Name = "肠球菌科",
                Target = "利奈唑胺",
                S = "≤2",
                I = "4",
                R = "≥8",
            });
            sIRRepository.Add(new Table_SIR
            {
                CN_Name = "肠球菌科",
                Target = "红霉素",
                S = "≤0.5",
                I = "1-4",
                R = "≥8",
            });


        }
        private void ddddd()
        {
            medicineRepository.Add(new Table_medicine
            {
                CN_Name = "青霉素",
                EN_Name = "Penicillin",
                Abbreviation = "PEN"
            });
            medicineRepository.Add(new Table_medicine
            {
                CN_Name = "左氧氟沙星",
                EN_Name = "Levofloxacin",
                Abbreviation = "LVX"
            });
            medicineRepository.Add(new Table_medicine
            {
                CN_Name = "头孢洛林",
                EN_Name = "",
                Abbreviation = ""
            });
            medicineRepository.Add(new Table_medicine
            {
                CN_Name = "环丙沙星",
                EN_Name = "",
                Abbreviation = ""
            });
            medicineRepository.Add(new Table_medicine
            {
                CN_Name = "克林霉素",
                EN_Name = "Clindamycin",
                Abbreviation = "CLI"
            });
            medicineRepository.Add(new Table_medicine
            {
                CN_Name = "达巴万星",
                EN_Name = "",
                Abbreviation = ""
            });
            medicineRepository.Add(new Table_medicine
            {
                CN_Name = "达托霉素",
                EN_Name = "",
                Abbreviation = ""
            });
            medicineRepository.Add(new Table_medicine
            {
                CN_Name = "红霉素",
                EN_Name = "Erythromycin",
                Abbreviation = "ERY"
            });
            medicineRepository.Add(new Table_medicine
            {
                CN_Name = "庆大霉素",
                EN_Name = "",
                Abbreviation = ""
            });
            medicineRepository.Add(new Table_medicine
            {
                CN_Name = "莫西沙星",
                EN_Name = "",
                Abbreviation = ""
            });
            medicineRepository.Add(new Table_medicine
            {
                CN_Name = "利奈唑胺",
                EN_Name = "Linezolid",
                Abbreviation = "LNZ"
            });
            medicineRepository.Add(new Table_medicine
            {
                CN_Name = "莫匹罗星",
                EN_Name = "",
                Abbreviation = ""
            });
            medicineRepository.Add(new Table_medicine
            {
                CN_Name = "呋喃妥因",
                EN_Name = "",
                Abbreviation = ""
            });
            medicineRepository.Add(new Table_medicine
            {
                CN_Name = "利福平",
                EN_Name = "",
                Abbreviation = ""
            });
            medicineRepository.Add(new Table_medicine
            {
                CN_Name = "链霉素",
                EN_Name = "",
                Abbreviation = ""
            });
            medicineRepository.Add(new Table_medicine
            {
                CN_Name = "四环素",
                EN_Name = "",
                Abbreviation = ""
            });
            medicineRepository.Add(new Table_medicine
            {
                CN_Name = "替考拉宁",
                EN_Name = "",
                Abbreviation = ""
            });
            medicineRepository.Add(new Table_medicine
            {
                CN_Name = "替加环素",
                EN_Name = "",
                Abbreviation = ""
            });
            medicineRepository.Add(new Table_medicine
            {
                CN_Name = "阿洛西林",
                EN_Name = "Azlocillin",
                Abbreviation = "SXT"
            });
        }

        /// <summary>
        /// 打开时读取数据库
        /// </summary>
        /// <returns></returns>
        public bool InitDataAsync()
        {
            try
            {
                if (File.Exists(DataPath.DataConnectionString))
                {
                    unitOfWork.OpenDB();
                }
                else
                {
                    unitOfWork.OpenDB();
                    unitOfWork.InitDB();
                    
                }

                if (File.Exists(DataPath.UserConnectionPath))
                    return true;
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                // TODO
                Biotech.Log.LoggerFactory.Instance.CurrentLogger.LogError(ex.Message);
                return false;
            }
        }

        /// <summary>
        /// 导入excel到数据库
        /// </summary>
        public void Export()
        {
            SQLiteConnection db = Connect();
            string excelFile = @"D:\Project\project-b12\project-b12-app\Doc\格式信息.xlsx";
            var dataTable = ReadExcelToDataSet(excelFile);
            int columnCount = dataTable.Columns.Count;
            StringBuilder values = new StringBuilder();
            for(int i = 0;i<columnCount;i++)
            {
                values.Append($"@Column{i}");
                if(i < columnCount)
                    values.Append(",");
            }

            string addsql = $"INSERT INTO TB_bacteria VALUES (@Column1, @Column2, @Column3,@Column4,@Column5,@Column6," +
                $"@Column7,@Column8,@Column9,@Column10,@Column11,@Column12,@Column13,@Column14)";

            using (var bulkInsertCommand = new SQLiteCommand(addsql, db))
            {
                for(int i = 0; i < columnCount; i++)
                {
                    bulkInsertCommand.Parameters.AddWithValue("@Column1", dataTable.Rows[0][0]);
                }

                bulkInsertCommand.Parameters.AddWithValue("@Column1", dataTable.Rows[0][0]);
                bulkInsertCommand.Parameters.AddWithValue("@Column2", dataTable.Rows[0][1]);
                bulkInsertCommand.Parameters.AddWithValue("@Column3", dataTable.Rows[0][2]);
                bulkInsertCommand.Parameters.AddWithValue("@Column4", dataTable.Rows[0][3]);
                bulkInsertCommand.Parameters.AddWithValue("@Column5", dataTable.Rows[0][4]);
                bulkInsertCommand.Parameters.AddWithValue("@Column6", dataTable.Rows[0][5]);
                bulkInsertCommand.Parameters.AddWithValue("@Column7", dataTable.Rows[0][6]);
                bulkInsertCommand.Parameters.AddWithValue("@Column8", dataTable.Rows[0][7]);
                bulkInsertCommand.Parameters.AddWithValue("@Column9", dataTable.Rows[0][8]);
                bulkInsertCommand.Parameters.AddWithValue("@Column10", dataTable.Rows[0][9]);
                bulkInsertCommand.Parameters.AddWithValue("@Column11", dataTable.Rows[0][10]);
                bulkInsertCommand.Parameters.AddWithValue("@Column12", dataTable.Rows[0][11]);
                bulkInsertCommand.Parameters.AddWithValue("@Column13", dataTable.Rows[0][12]);
                bulkInsertCommand.Parameters.AddWithValue("@Column14", dataTable.Rows[0][13]);

                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    bulkInsertCommand.Parameters["@Column1"].Value = dataTable.Rows[i][0];
                    bulkInsertCommand.Parameters["@Column2"].Value = dataTable.Rows[i][1];
                    bulkInsertCommand.Parameters["@Column3"].Value = dataTable.Rows[i][2];
                    bulkInsertCommand.Parameters["@Column4"].Value = dataTable.Rows[i][3];
                    bulkInsertCommand.Parameters["@Column5"].Value = dataTable.Rows[i][4];
                    bulkInsertCommand.Parameters["@Column6"].Value = dataTable.Rows[i][5];
                    bulkInsertCommand.Parameters["@Column7"].Value = dataTable.Rows[i][6];
                    bulkInsertCommand.Parameters["@Column8"].Value = dataTable.Rows[i][7];
                    bulkInsertCommand.Parameters["@Column9"].Value = dataTable.Rows[i][8];
                    bulkInsertCommand.Parameters["@Column10"].Value = dataTable.Rows[i][9];
                    bulkInsertCommand.Parameters["@Column11"].Value = dataTable.Rows[i][10];
                    bulkInsertCommand.Parameters["@Column12"].Value = dataTable.Rows[i][11];
                    bulkInsertCommand.Parameters["@Column13"].Value = dataTable.Rows[i][12];
                    bulkInsertCommand.Parameters["@Column14"].Value = dataTable.Rows[i][13];
                    bulkInsertCommand.ExecuteNonQuery();
                }
            }
            db.Close();
        }

        public DataTable ReadExcelToDataSet(string filePath)
        {
            DataTable dataTable = null;
            FileStream fs = null;
            DataColumn column = null;
            DataRow dataRow = null;
            IWorkbook workbook = null;
            ISheet sheet = null;
            IRow row = null;
            ICell cell = null;
            int startRow = 0;
            bool isColumnName = true;
            try
            {
                using (fs = System.IO.File.OpenRead(filePath))
                {
                    // 2007版本  
                    if (filePath.IndexOf(".xlsx") > 0)
                        workbook = new XSSFWorkbook(fs);
                    // 2003版本  
                    else if (filePath.IndexOf(".xls") > 0)
                        workbook = new HSSFWorkbook(fs);
                    if (workbook != null)
                    {
                        sheet = workbook.GetSheetAt(0);//读取第一个sheet，当然也可以循环读取每个sheet  
                        dataTable = new DataTable();
                        if (sheet != null)
                        {
                            int rowCount = sheet.LastRowNum;//总行数  
                            if (rowCount > 0)
                            {
                                IRow firstRow = sheet.GetRow(0);//第一行  
                                int cellCount = firstRow.LastCellNum;//列数  

                                //构建datatable的列  
                                if (isColumnName)
                                {
                                    startRow = 1;//如果第一行是列名，则从第二行开始读取  
                                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                                    {
                                        cell = firstRow.GetCell(i);
                                        if (cell != null)
                                        {
                                            if (cell.StringCellValue != null)
                                            {
                                                column = new DataColumn(cell.StringCellValue);
                                                dataTable.Columns.Add(column);
                                            }
                                        }
                                    }
                                }
                                else
                                {
                                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                                    {
                                        column = new DataColumn("column" + (i + 1));
                                        dataTable.Columns.Add(column);
                                    }
                                }

                                //填充行  
                                for (int i = startRow; i <= rowCount; ++i)
                                {
                                    row = sheet.GetRow(i);
                                    if (row == null) continue;

                                    dataRow = dataTable.NewRow();
                                    for (int j = row.FirstCellNum; j < cellCount; ++j)
                                    {
                                        cell = row.GetCell(j);
                                        if (cell == null)
                                        {
                                            dataRow[j] = "";
                                        }
                                        else
                                        {
                                            //CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)  
                                            switch (cell.CellType)
                                            {
                                                case CellType.Blank:
                                                    dataRow[j] = "";
                                                    break;
                                                case CellType.Numeric:
                                                    short format = cell.CellStyle.DataFormat;
                                                    //对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理  
                                                    if (format == 14 || format == 31 || format == 57 || format == 58)
                                                        dataRow[j] = cell.DateCellValue;
                                                    else
                                                        dataRow[j] = cell.NumericCellValue;
                                                    break;
                                                case CellType.String:
                                                    dataRow[j] = cell.StringCellValue;
                                                    break;
                                            }
                                        }
                                    }
                                    dataTable.Rows.Add(dataRow);
                                }
                            }
                        }
                    }
                }
                
            }
            catch (Exception ex)
            {
                if (fs != null)
                {
                    fs.Close();
                }
                throw ex;
            }
            return dataTable;
        }

        private SQLiteConnection Connect()
        {
            var dbConnect=new SQLiteConnection(DataPath.DataConnectionString);
            dbConnect.Open();
            return dbConnect;
        }
    }
}
